package inv.loader.logic.impl;

import inv.loader.logic.LoaderException;
import inv.loader.logic.TableExport;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class XLSTableExport implements TableExport {

	public boolean exportData(OutputStream stream, List<?> data,
			String[] properties) throws LoaderException {
		
		Workbook wb = new HSSFWorkbook();
		CreationHelper createHelper = wb.getCreationHelper();
		Sheet sheet1 = wb.createSheet("export");
		
		CellStyle dateStyle = wb.createCellStyle();
		dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-mm-yyyy"));
		
		// Header cells
		int rowNo = 0;
		Row row = sheet1.createRow(rowNo++);
		int pos = 0;
		for(String property: properties){
			row.createCell(pos++).setCellValue(property);
		}

		// Data
		for(Object obj: data){
			row = sheet1.createRow(rowNo++);
			pos = 0;
			for(String property: properties){
				try {
					setCellValue( row.createCell(pos++), PropertyUtils.getProperty(obj, property), dateStyle);
				} catch (IllegalAccessException e) {
					e.printStackTrace();
					throw new LoaderException(e.getMessage());
				} catch (InvocationTargetException e) {
					e.printStackTrace();
					throw new LoaderException(e.getMessage());
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
					throw new LoaderException(e.getMessage());
				}
			}
		}

		try {
			wb.write(stream);
		} catch (IOException e) {
			e.printStackTrace();
			throw new LoaderException(e.getMessage());
		}
		
		return true;

	}
	
	private void setCellValue(Cell cell, Object value, CellStyle dateStyle){
		
		if(value instanceof Boolean){
			cell.setCellValue((Boolean)value);
			cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
		}
		else
			if(value instanceof Calendar){
				cell.setCellValue((Calendar)value);
				cell.setCellStyle(dateStyle);
			}
			else
				if(value instanceof Date){
					cell.setCellValue((Date)value);
					cell.setCellStyle(dateStyle);
				}
				else
					if(value instanceof Double){
						cell.setCellValue((Double)value);
						cell.setCellType(Cell.CELL_TYPE_NUMERIC);
					}
					else
						if(value instanceof Integer){
							cell.setCellValue((Integer)value);
							cell.setCellType(Cell.CELL_TYPE_NUMERIC);
						}
						else
							if(value instanceof String){
								cell.setCellValue((String)value);
								cell.setCellType(Cell.CELL_TYPE_STRING);
							}
							else
								if(value instanceof RichTextString){
									cell.setCellValue((RichTextString)value);
									cell.setCellType(Cell.CELL_TYPE_NUMERIC);
								}
								else{
									cell.setCellValue(String.valueOf(value));
									cell.setCellType(Cell.CELL_TYPE_STRING);
								}
	}

	public boolean exportDataWithAttributes(OutputStream stream, List<?> data,
			String[] properties) throws LoaderException {
		return exportData(stream, data, properties);
	}

	public <T> List<T> importData(Class<T> beanClass, InputStream stream) throws LoaderException{
		Workbook wb	= null;
		try {
			wb = WorkbookFactory.create(stream);
		} catch (Exception e) {
			e.printStackTrace();
		}
	    Sheet sheet = wb.getSheetAt(0);
	    Row headerRow	= sheet.getRow(0);
	    int index = 0;
	    Cell cell		= headerRow.getCell(index++);
	    List<String> properties = new ArrayList<String>();
	    while(cell != null){
	    	String cellValue = cell.getStringCellValue();
	    	properties.add(cellValue);
	    	cell		= headerRow.getCell(index++);
	    }
	    
	    List<T> returnList	= new ArrayList<T>();
	    for(int rowIndex=1;;rowIndex++){
	    	T obj	= null;
			try {
				obj = beanClass.getConstructor().newInstance();
			} catch (Exception e) {
				e.printStackTrace();
				throw new LoaderException(e.getMessage());
			} 
			if(obj == null)
				return null;
			
	    	Row row	= sheet.getRow(rowIndex);
	    	if(row == null)
	    		break;
	    	
	    	for(int i=0;i<properties.size();i++){
	    		cell = row.getCell(i);
	    		if(cell == null)
	    			break;
	    		
	    		Object value = readCellValue(wb, cell);
	    		String name = properties.get(i);
	    		try {
					PropertyUtils.setProperty(obj, name, value);
				} catch (Exception e) {
					e.printStackTrace();
					throw new LoaderException(e.getMessage());
				} 
	    	}
	    	
	    	returnList.add(obj);
	    }

		return returnList;
	}
	
	private Object readCellValue(Workbook workbook, Cell cell){
		switch(cell.getCellType()) {
	      case Cell.CELL_TYPE_STRING:
	        return cell.getRichStringCellValue().getString();
	      case Cell.CELL_TYPE_NUMERIC:
	        if(DateUtil.isCellDateFormatted(cell)) {
	          return cell.getDateCellValue();
	        } else {
	        	
	        	short dataFormatNr = cell.getCellStyle().getDataFormat();
	        	DecimalFormat df = null;

	        	if (dataFormatNr > 0 && dataFormatNr < HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) {

	        	        df = new DecimalFormat(HSSFDataFormat.getBuiltinFormat(dataFormatNr));
	        	}
	        	else {
	        	        df = new DecimalFormat();
	        	        String formatStr = workbook.createDataFormat().getFormat(dataFormatNr);
	        	        if (!formatStr.toUpperCase().equals("GENERAL")) {
	        	                try
	        	                {
	        	                        df.applyPattern(formatStr);
	        	                }
	        	                catch (IllegalArgumentException e) {
	        	                }
	        	        }
	        	}

	        	String value = df.format(cell.getNumericCellValue());
	        	
	        	Object retValue = null;
	        	try{
	        		retValue = Integer.parseInt(value);
	        	}catch(NumberFormatException nfe){
	        		retValue = Double.parseDouble(value);
	        	}
	        	
	          return retValue;
	        }
	      case Cell.CELL_TYPE_BOOLEAN:
	    	  return cell.getBooleanCellValue();
	      case Cell.CELL_TYPE_FORMULA:
	    	  return cell.getCellFormula();
	      default:
	    	  return cell.getStringCellValue();
			}

	}

}
